package com.exam.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.exam.model.QuestionForm;
import com.exam.model.QuestionModel;
import com.exam.util.ConnDB;
import com.exam.util.Tools;


public class QuestionDao extends BaseDao{
	
	
	public ArrayList<QuestionForm> getErrorQuestions(String errQids) throws SQLException{
		
		ArrayList<QuestionForm> list = new ArrayList<QuestionForm>();
		QuestionForm qf = null;
		String sql = "SELECT " +
				"id," +
				"content," +
				"answer," +
				"answer_info " +
				" FROM ex_question WHERE " +
				" id IN("+errQids+")";
		System.out.println(sql);
		
		res = query(sql);
		while(res.next()){
			qf = new QuestionForm();
			qf.setId(res.getInt(1));
			qf.setContent(res.getString(2));
			qf.setAnswer(res.getString(3));
			qf.setAnswer_info(res.getString(4));
			
			String sql1 = "select salisa,options from ex_question_options where qid="+qf.getId() +" order by salisa asc";
			System.out.println(sql1);
			Map<String,String> options = new LinkedHashMap<String,String>();
			Connection conn2 = ConnDB.getConn();
			res2 = conn2.createStatement().executeQuery(sql1);
			while(res2.next()){  //获取试题的选项
				options.put(res2.getString(1), res2.getString(2));
				qf.getOption().add(res2.getString(2));
			}
			ConnDB.closeResultSet(res2);
			ConnDB.closeDB(conn2);
			list.add(qf);
		}
	
		ConnDB.closeResultSet(res);
		closeDB();
		return list;
		
	}
	
	/**
	 * 获取试卷试题
	 * @param 
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<QuestionForm> getOneQuestionForPaper(int qNum,int dbid,int type) throws SQLException{
		
		ArrayList<QuestionForm> list = new ArrayList<QuestionForm>();
		QuestionForm qf = null;
		String sql = "";
		System.out.println(type);
		if(type==-1){  //随机取出试题
			sql = "select " +
					"id," +
					"content," +
					"answer " +
					" from ex_question where " +
					" status=1 and dbid="+dbid+" ORDER BY rand() limit 0,"+qNum;
		}else{
			sql = "select " +
					"id," +
					"content," +
					"answer " +
					" from ex_question where " +
					"status=1 and dbid="+dbid+" limit 0,"+qNum;
		}
		
		System.out.println(sql);
		res = query(sql);
		while(res.next()){
			qf = new QuestionForm();
			qf.setId(res.getInt(1));
			qf.setContent(res.getString(2));
			qf.setAnswer(res.getString(3));
			
			String sql1 = "select salisa,options from ex_question_options where qid="+qf.getId() +" order by salisa asc";
			System.out.println(sql1);
			Map<String,String> options = new LinkedHashMap<String,String>();
			Connection conn2 = ConnDB.getConn();
			res2 = conn2.createStatement().executeQuery(sql1);
			while(res2.next()){  //获取试题的选项
				options.put(res2.getString(1), res2.getString(2));
				qf.getOption().add(res2.getString(2));
			}
			ConnDB.closeResultSet(res2);
			ConnDB.closeDB(conn2);
			list.add(qf);
		}
	
		ConnDB.closeResultSet(res);
		closeDB();
		return list;
	}
	
	
	/**
	 * 添加试题
	 * @throws SQLException 
	 */
	public boolean addOneQuestion(QuestionForm qfm) throws SQLException{
		
		String sql = "insert into ex_question ( " +
				" dbid,answer,content,answer_info,status" +
				" )values( "+qfm.getDbid()+"," +
						"'"+qfm.getAnswer()+"'," +
						"'"+qfm.getContent()+"'," +
						"'"+qfm.getAnswer_info()+"'," +
						" "+qfm.getStatus()+" )";
		System.out.println(sql);
		if(adu(sql)){
			
			int qid = 0;
			String sql1 = "select MAX(id) from ex_question";
			res = query(sql1);
			if(res.next()){
				qid = res.getInt(1);  //获取试题id
			}
			closeDB();
			System.out.println("试题id"+qid);
			List<String> options = qfm.getOption();
			conn = ConnDB.getConn();
			String sqls ="insert into ex_question_options ( qid,salisa,options ) values ("+qid+",?,?)";
			pstm = conn.prepareStatement(sqls);
			String  salisa = "ABCD";
			for(int i=0 ;i<options.size() ;i++){
				
				pstm.setString(1, salisa.charAt(i)+"");
				pstm.setString(2, options.get(i));
				System.out.println(options.get(i));
				pstm.addBatch();
			}
			int[] results = pstm.executeBatch();
			System.out.println(results[0]+results[1]+results[2]+results[3]);
			closeDB();
			
		}else{
			return false;
		}
		
		return true;
	}
	

	
	/**
	 * 根据条件查询
	 * @param dbid
	 * @param limit
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<QuestionModel> getQuestionList(String key, String dbid,int status,String limit) throws SQLException{
		
		String condition = "";
		if(!key.equals("")){
			condition += " and content like '%"+key+"%'";
		}
		if(status==0 || status==1){
			condition += " and status="+status;
		}
		if(dbid!=null){
			if(Tools.isNumeric(dbid)){
				condition += " and dbid="+dbid;
			}
		}
		
		String sql = "select " +
				"id," +
				"content," +
				"answer," +
				"answer_info," +
				"status," +
				"dbid," +
				"( select db_name from ex_question_db where id=dbid ) dbName " +
				" from ex_question " +
				" where dbid in (select id from ex_question_db where db_user='"+getAdmin()+"' )  "+condition+" "+limit;
		System.out.println(sql);
		res = query(sql);
		ArrayList<QuestionModel> list = new ArrayList<QuestionModel>();
		while(res.next()){
			QuestionModel qm = new QuestionModel();
			qm.setId(res.getInt(1));
			qm.setContent(Tools.subStr(res.getString(2), 30));
			qm.setAnswer(res.getString(3));
			qm.setAnswer_info(Tools.subStr(res.getString(4), 20));
			qm.setStatus(res.getInt(5));
			qm.setDbid(res.getInt(6));
			qm.setDb_name(res.getString(7));
			list.add(qm);
		}
		
		closeDB();
		return list;
	}
	
	public int getCount(String key, String dbid,int status){
		
		String condition = "";
		if(!key.equals("")){
			condition += " and content like '%"+key+"%'";
		}
		if(status==0 || status==1){
			condition += " and status="+status;
		}
		if(dbid!=null){
			if(Tools.isNumeric(dbid)){
				condition += " and dbid="+dbid;
			}
		}
		
		
		String sql = "select count(*) from ex_question where id<>0 "+condition;
		int m = 0;
		res = query(sql);
		try {
			if(res.next()){
				m = res.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(m+" 条记录");
		closeDB();
		return m;
	}
	
	/*
	 * 获取每个题库的总记录
	 * (non-Javadoc)
	 * @see com.exam.dao.BaseDao#getCount(java.lang.String)
	 */
	public int getCount(String dbid){
		String sql = "select count(*) from ex_question where dbid="+dbid;
		int m = 0;
		res = query(sql);
		try {
			if(res.next()){
				m = res.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		closeDB();
		return m;
	}
	
	
	/**
	 * 获取一条试题信息
	 * @param qid
	 * @return
	 * @throws SQLException
	 */
	public QuestionForm getOneQuestion(String qid) throws SQLException{

		QuestionForm qf = null;
		String sql = "select id," +
				"content," +
				"answer," +
				"answer_info," +
				"status," +
				"dbid," +
				"( select db_name from ex_question_db where id=dbid ) dbName " +
				" from ex_question where id="+qid;
		res = query(sql);
		if(res.next()){
			qf = new QuestionForm();
			qf.setId(res.getInt(1));
			qf.setContent(res.getString(2));
			qf.setAnswer(res.getString(3));
			qf.setAnswer_info(res.getString(4));
			qf.setStatus(res.getInt(5));
			qf.setDbid(res.getInt(6));
			qf.setDb_name(res.getString(7));			
		}
		
		String sql1 = "select options from ex_question_options where qid="+qid +" order by salisa asc";
		List<String> options = new ArrayList<String>();
		res = query(sql1);
		while(res.next()){
			options.add(res.getString(1));
		}
		qf.setOption(options);

		closeDB();
		return qf;
	}
	
	
	/**
	 * 修改试题
	 * @param qfm
	 * @return
	 * @throws SQLException
	 */
	public boolean updateQuestion(QuestionForm qfm) throws SQLException{
		String sql = "update ex_question set " +
				" dbid="+qfm.getDbid()+"," +
				"answer='"+qfm.getAnswer()+"'," +
				"content='"+qfm.getContent()+"'," +
				"answer_info='"+qfm.getAnswer_info()+"'," +
				"status="+qfm.getStatus()+" where id="+qfm.getId();
		
		

		List<String> options = qfm.getOption();
		conn = ConnDB.getConn();
		String sql1 ="update ex_question_options set options=? where salisa=? and qid="+qfm.getId();
		pstm = conn.prepareStatement(sql1);
		String  salisa = "ABCD";
		for(int i=0 ;i<options.size() ;i++){
			pstm.setString(1, options.get(i));
			
			pstm.setString(2, salisa.charAt(i)+"");
			
			System.out.println(options.get(i));
			
			pstm.addBatch();
		}
		 pstm.executeBatch();
		
		closeDB();
		return adu(sql);
	}
	
	
	/**
	 * 更改状态
	 * @param qid
	 * @param status
	 * @return
	 */
	public boolean changeStatus(String qid , int status){
		String sql = "update ex_question set status="+status +" where id="+qid;
		return adu(sql);
	}
	
	
	/**
	 * 删除试题
	 * @param id
	 * @return
	 */
	public boolean deleteQuestion(String id){
		
		String sql1 = "delete from ex_question_options where qid="+id;
		String sql2 = "delete from ex_question where id="+id;
		return adu(sql1)&adu(sql2);
	}
	
	
}
